﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;

namespace Site.Secure
{
    public partial class ManualEnter : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        bool TableContains(string Table, string Field, string Value)
        {
            SqlDataSource1.SelectCommand = "SELECT " + Field + " FROM " + Table + " WHERE " + Field + "='" + Value + "'";
            DataView View = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());
            if (View.Count == 0)
                return false;
            else
                return true;
        }

        int StateFromHiveData(string Site)
        {
            return 0;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile)
            {
                try
                {
                    string filename = Path.GetFileName(FileUpload1.FileName);
                    string sLine = "";
                    string[] Items;
                    FileUpload1.SaveAs(Server.MapPath("~/") + filename);

                    System.IO.FileStream file = System.IO.File.Open(Server.MapPath("~/") + filename, FileMode.Open);
                    System.IO.StreamReader sr = new StreamReader(file);
                    while (!sr.EndOfStream)
                    {
                        sLine = sr.ReadLine();
                        ListBox1.Items.Add(sLine);
                        Items = sLine.Split('|');

                        DateTime td = DateTime.Parse(Items[16]);

                        string Date = td.ToShortDateString();
                        string Time = td.ToShortTimeString();

                        if (Items[0] == "" || Items[1] == "")
                            return;
                        if (Items[0] == null || Items[1] == null)
                            return;

                        string InsertCommand = "INSERT INTO DataTable(Barcode, SiteID, SupersCount, Brood, FullFrames, Feed, Capped, Feeding, Attention, Harvest, Problems, Queen, GPS, Date, Time, PID, ID) VALUES(";
                        InsertCommand += "'" + Items[0] + "', ";
                        InsertCommand += "'" + Items[1] + "', ";
                        InsertCommand += "'" + Items[2] + "', ";
                        InsertCommand += "'" + Items[3] + "', ";
                        InsertCommand += "'" + Items[4] + "', ";
                        InsertCommand += "'" + Items[5] + "', ";
                        InsertCommand += "'" + Items[6] + "', ";
                        InsertCommand += "'" + Items[7] + "', ";
                        InsertCommand += "'" + Items[8] + "', ";
                        InsertCommand += "'" + Items[9] + "', ";
                        InsertCommand += "'" + Items[10] + "', ";
                        InsertCommand += "'" + Items[11] + "', ";
                        InsertCommand += "'" + Items[15] + "', ";
                        InsertCommand += "'" + Date + "', ";
                        InsertCommand += "'" + Time + "',";
                        InsertCommand += "'" + "UNKNOWN" + "',";
                        InsertCommand += "'" + Guid.NewGuid() +"'";
                        InsertCommand += ")";
                        SqlDataSource1.InsertCommand = InsertCommand;
                        SqlDataSource1.Insert();


                        string[] sItems;
                        string sLong = "";
                        string sLat = "";
                        sItems = Items[15].Split('S');

                        if (sItems.Length == 2)
                        {

                            sLong = sItems[0];
                            sLat = sItems[1];

                            sLong = sLong.Replace('P', '.');
                            sLong = sLong.Replace('N', '-');
                            sLat = sLat.Replace('P', '.');
                            sLat = sLat.Replace('N', '-');
                        }

                        if (TableContains("Hives", "Barcode", Items[0]) == true)
                        {
                            SqlDataSource1.UpdateCommand = "UPDATE Hives Set SupersCount='" + Items[2] + "', ";
                            SqlDataSource1.UpdateCommand += "Brood='" + Items[3] + "', ";
                            SqlDataSource1.UpdateCommand += "FullFrames='" + Items[4] + "', ";
                            SqlDataSource1.UpdateCommand += "Feed='" + Items[5] + "', ";
                            SqlDataSource1.UpdateCommand += "Capped='" + Items[6] + "', ";
                            SqlDataSource1.UpdateCommand += "Feeding='" + Items[7] + "', ";
                            SqlDataSource1.UpdateCommand += "Attention='" + Items[8] + "', ";
                            SqlDataSource1.UpdateCommand += "Harvest='" + Items[9] + "', ";
                            SqlDataSource1.UpdateCommand += "Problems='" + Items[10] + "', ";
                            SqlDataSource1.UpdateCommand += "Queen='" + Items[11] + "' ";
                            SqlDataSource1.UpdateCommand += "WHERE Barcode='" + Items[2] + "'";
                            SqlDataSource1.Update();
                        }
                        else
                        {
                            string sInsertCommand = "INSERT INTO Hives(Barcode, SiteID, SupersCount, Brood, FullFrames, Feed, Capped, Feeding, Attention, Harvest, Problems, Queen) VALUES(";
                            sInsertCommand += "'" + Items[0] + "', ";
                            sInsertCommand += "'" + Items[1] + "', ";
                            sInsertCommand += "'" + Items[2] + "', ";
                            sInsertCommand += "'" + Items[3] + "', ";
                            sInsertCommand += "'" + Items[4] + "', ";
                            sInsertCommand += "'" + Items[5] + "', ";
                            sInsertCommand += "'" + Items[6] + "', ";
                            sInsertCommand += "'" + Items[7] + "', ";
                            sInsertCommand += "'" + Items[8] + "', ";
                            sInsertCommand += "'" + Items[9] + "', ";
                            sInsertCommand += "'" + Items[10] + "', ";
                            sInsertCommand += "'" + Items[11] + "')";
                            SqlDataSource1.InsertCommand = sInsertCommand;
                            SqlDataSource1.Insert();
                        }


                        if (TableContains("Sites", "SiteID", Items[1]))
                        {
                            SqlDataSource1.UpdateCommand = "UPDATE Sites SET lastupdatedTime='" + DateTime.Now.ToShortTimeString() + "', ";
                            SqlDataSource1.UpdateCommand += "lastupdatedDate='" + DateTime.Now.ToShortDateString() + "', ";
                            SqlDataSource1.UpdateCommand += "weatherType='" + "0" + "', ";
                            SqlDataSource1.UpdateCommand += "weatherTemp='" + "25" + "', ";
                            if (Items.Length == 2)
                            {
                                SqlDataSource1.UpdateCommand += "poslong='" + sLong + "', ";
                                SqlDataSource1.UpdateCommand += "posLat='" + sLat + "', ";
                            }
                            SqlDataSource1.UpdateCommand += "State='" + StateFromHiveData(Items[1]) + "'";
                            SqlDataSource1.UpdateCommand += " WHERE SiteID='" + Items[1] + "'";
                            SqlDataSource1.Update();
                        }
                        else
                        {
                            SqlDataSource1.InsertCommand = "INSERT INTO Sites(SiteID, posLong, posLat, lastupdatedTime, lastupdatedDate, weatherType, weatherTemp, beekeeperID, State) VALUES ('";
                            SqlDataSource1.InsertCommand += Items[1] + "', '";
                            SqlDataSource1.InsertCommand += sLong + "', '";
                            SqlDataSource1.InsertCommand += sLat + "', '";
                            SqlDataSource1.InsertCommand += DateTime.Now.ToShortTimeString() + "', '";
                            SqlDataSource1.InsertCommand += DateTime.Now.ToShortDateString() + "', '";
                            SqlDataSource1.InsertCommand += "0" + "', '";
                            SqlDataSource1.InsertCommand += "25" + "','";
                            SqlDataSource1.InsertCommand += "UNKNOWN" + "', '";
                            SqlDataSource1.InsertCommand += "0" + "') ";
                            SqlDataSource1.Insert();
                        }

                    }
                    sr.Close();
                    sr.Dispose();

                    file.Close();
                    file.Dispose();

                    File.Delete(Server.MapPath("~/") + filename);
                }
                catch (Exception ex)
                {
                    Label1.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
                }
            }
        }
    }
}